R Notebook

#loading the necessary packages
library(tidyverse)
library(janitor)
library(readxl)
library(here)

#loading the data
candy_2015 <- read_excel("../raw_data/boing-boing-candy-2015.xlsx") %>% 
  clean_names()
candy_2016 <- read_excel("../raw_data/boing-boing-candy-2016.xlsx") %>% 
  clean_names()
candy_2017 <- read_excel("../raw_data/boing-boing-candy-2017.xlsx") %>% 
  clean_names()

New names:

candy_2016

candy_2015

candy_2017

#order of operations: #decide which variables to keep for the analysis according to business questions # Pivot data the same for all 3 datasets # perform pivot on all three # Investigate other columns

#only keeping variables useful for analysis from 2015 dataset #The variables are a bit confusing. I will only keep variables that are actual food and drop anything which is not food.

candy_2015 <- candy_2015 %>% 
  select(-c(
    cash_or_other_forms_of_legal_tender, 
    creepy_religious_comics_chick_tracts, 
    hugs_actual_physical_hugs, 
    please_leave_any_remarks_or_comments_regarding_your_choices: 
    please_estimate_the_degrees_of_separation_you_have_from_the_following_folks_beyonce_knowles, 
    dental_paraphenalia, 
    generic_brand_acetaminophen, 
    peterson_brand_sidewalk_chalk
))
candy_2015

#time to rename some columns. Maybe not that necessary but I would like to have my final results with neat names.

candy_2015 <- candy_2015 %>% 
  rename("trick_or_treat" = are_you_going_actually_going_trick_or_treating_yourself,
         "grand_bar" = x100_grand_bar, 
         "brown_globs" = anonymous_brown_globs_that_come_in_black_and_orange_wrappers, 
         "any_candy_bar" = any_full_sized_candy_bar, 
         "brach_without_candy_corn" = brach_products_not_including_candy_corn, 
         "high_fructose_corn_syrup" = vials_of_pure_high_fructose_corn_syrup_for_main_lining_into_your_vein, 
         "restaurant_candy" = candy_that_is_clearly_just_the_stuff_given_out_for_free_at_restaurants, 
         "chick_o_sticks"  = chick_o_sticks_we_don_t_know_what_that_is, 
         "old_marshmallow" = those_odd_marshmallow_circus_peanut_things)
candy_2015

#same thing for the 2016 dataset

#selecting only the necessary columns for final analysis
candy_2016 <- candy_2016 %>% 
  select(-c(cash_or_other_forms_of_legal_tender, 
            creepy_religious_comics_chick_tracts, 
            dental_paraphenalia, 
            generic_brand_acetaminophen, 
            hugs_actual_physical_hugs, 
            person_of_interest_season_3_dvd_box_set_not_including_disc_4_with_hilarious_outtakes, 
            sourpatch_kids_i_e_abominations_of_nature, 
            vials_of_pure_high_fructose_corn_syrup_for_main_lining_into_your_vein, 
            please_list_any_items_not_included_above_that_give_you_joy:
              york_peppermint_patties_ignore))
candy_2016

candy_2016 <- candy_2016 %>% 
  rename("trick_or_treat" = are_you_going_actually_going_trick_or_treating_yourself, 
         "gender" = your_gender, 
         "age" = how_old_are_you, 
         "country" = which_country_do_you_live_in, 
         "state_province" = which_state_province_county_do_you_live_in, 
         "grand_bar" = x100_grand_bar, 
         "brown_globs" = anonymous_brown_globs_that_come_in_black_and_orange_wrappers, 
         )
candy_2016

#candy 2017

candy_2017 <- candy_2017 %>% 
  select(-c(q7_joy_other : click_coordinates_x_y))
candy_2017

#clean column: removing the q+numnber_ before variable name

candy_2017 <- candy_2017 %>% 
  rename_with(~ str_remove(., pattern = "q[0-90-9]+_"))
candy_2017

#pivot longer all the datasets

library(tidyr)

#2015
candy_2015 <- candy_2015 %>% 
  pivot_longer("butterfinger":"york_peppermint_patties",
names_to = "candy_type", 
values_to = "rating")
candy_2015

#2016
candy_2016 <- candy_2016 %>% 
  pivot_longer("grand_bar":"york_peppermint_patties",
names_to = "candy_type", 
values_to = "rating")
candy_2016

#2017
candy_2017 <- candy_2017 %>% 
  pivot_longer("100_grand_bar":"york_peppermint_patties",
names_to = "candy_type", 
values_to = "rating")
candy_2017

#binding all the datasets
bind_rows(candy_2015, 
          candy_2016, 
          candy_2017, 
          .id = "year")

#sort out age column and country column

#binding the 3 datasets
candy <- bind_rows(candy_2015, 
                            candy_2016, 
                            candy_2017, 
                   .id = id)
candy

#let’s clean once again candy_type

candy <- candy %>% 
  mutate(candy_type = recode(candy_type,
                              "anonymous_brown_globs_that_come_in_black_and_orange_wrappers_a_k_a_mary_janes" = "mary_janes"),
  candy_type = recode(candy_type,"bonkers_the_candy" = "bonkers"),
  candy_type = recode(candy_type,"boxo_raisins" = "box_o_raisins"),
  candy_type = recode(candy_type,"licorice_yes_black" = "licorice"),
  candy_type = recode(candy_type,"sweetums_a_friend_to_diabetes" = "sweetums"))
  candy

#country column

candy <- candy %>% 
  mutate(country = if_else(grepl("(?i)usa+", country),"USA",country)) %>% 
  mutate(country = if_else(grepl("(?i)united s+", country),"USA",country)) %>% 
  mutate(country = if_else(grepl("(?i)amer", country),"USA",country)) %>% 
  mutate(country = if_else(grepl("(?i)stat", country),"USA",country)) %>% 
  mutate(country = if_else(grepl("(?i)subscribe+.*", country),NA_character_,country)) 

#make vectors of USA outliers and some to change to NA values
usa_outliers = c("Alaska", "California", "EUA", "Merica", "Murica", "murrika",
                 "New Jersey", "New York", "North Carolina", "Pittsburgh", 
                 "The Yoo Ess of Aaayyyyyy", "Trumpistan", "U S", "u s a", "u.s.",
                 "U.s.", "U.S.", "u.s.a.", "U.S.A.", "UD", "us", "Us", "US", "US of A",
                 "USSA", "'merica")
change_to_NA = c(1, 30.0, 32, 35, 44.0, 45, 45.0, 46, 47.0, 51.0, 54.0)
change_to_NA2 = c("30.0", "44.0", "45.0", "47.0", "51.0", "54.0")
others = c(
  "A tropical island south of the equator", "A", "Atlantis",
  "Canae", "cascadia ", "Cascadia", "Denial", "Earth", "Fear and Loathing", 
  "god's country", "I don't know anymore", "insanity lately", 
  "there isn't one for old men", "soviet canuckistan", "Narnia", "Neverland",
  "one of the best ones", "See above", "Somewhere", 
  "Subscribe To Dm4uz3 On Youtube", "The republic of Cascadia", "this one", 
  "Europe", " Cascadia", "Cascadia ")
candy

candy <- candy %>%
mutate(country = if_else(country %in% usa_outliers ,
                         "USA", country)) %>% 
  mutate(country = if_else(country %in% others|
                             country %in% change_to_NA|
                             country %in% change_to_NA2, 
                           NA_character_, country)) %>% 
  mutate(country = str_to_title(country)) 
candy

candy <- candy %>%
mutate(country = recode(country, "The Netherlands" = "Netherlands"),
       country = recode(country, "Can" = "Canada"),
       country = recode(country, "Canada`" = "Canada"),
       country = recode(country, "Endland" = "United Kingdom"),
       country = recode(country, "England" = "United Kingdom"),
       country = recode(country, "England" = "United Kingdom"),
       country = recode(country, "Scotland" = "United Kingdom"),
       country = recode(country, "España" = "Spain"),
       country = recode(country, "U.k." = "United Kingdom"),
       country = recode(country, "Uk" = "United Kingdom"),
       country = recode(country, "United Kindom" = "United Kingdom"))
candy

#age column

candy <- candy %>% 
  mutate(age = as.numeric(age)) %>% 
  count(age) %>% 
  mutate(age = ifelse(age>122, NA, age))

Warning: NAs introduced by coercion

candy

#write data to CSV

candy %>% 
  write_csv("../clean_data/candy_clean.csv")